package com.glSto.controller;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.sql.DataSource;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Pattern;
import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.*;

@RestController
@RequestMapping("/api/oa-application")
@Validated
public class OA_ZBXXMU {

   private static final Logger logger = LoggerFactory.getLogger(OA_ZBXXMU.class);
    private final JdbcTemplate jdbcTemplate;

    @Value("${file.upload.base-path:/opt/oa-system/uploads/}")
    private String fileBasePath;

    // 允许的文件后缀（小写）
    private static final List<String> ALLOWED_FILE_SUFFIX = Arrays.asList(
            ".pdf", ".doc", ".docx", ".xls", ".xlsx", ".ppt", ".pptx"
    );

    // 日期格式化器（线程安全）
    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd");
    private static final DateTimeFormatter DATETIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");

    // PB_DOCFILES固定值
    private static final String BIZAREAID = "00000000-0000-0000-0000-00000000000a";
    private static final String REGHUMID = "ad000000-0000-0000-0000-000000000000";
    private static final String REGHUMNAME = "系统管理员";
    private static final String BOKEYWORD = "OAJK_APPLICATION";


    public OA_ZBXXMU(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }


    /**
     * 提交OA申请（同时插入主表和文件表）
     */
    @PostMapping(value = "/OAJK_APPLICATION", consumes = "multipart/form-data")
    public Map<String, Object> submitOaApplication(
            // 基础必填字段
            @NotBlank(message = "申请人姓名（APPLICANT_NAME）不能为空")
            @RequestParam("applicantName") String applicantName,

            @NotBlank(message = "申请人部门（APPLICANT_DEPT）不能为空")
            @RequestParam("applicantDept") String applicantDept,

            @NotBlank(message = "联系电话（CONTACT_PHONE）不能为空")
            @Pattern(regexp = "^(1[3-9]\\d{9}|0\\d{2,3}-\\d{7,8})$", message = "联系电话格式错误")
            @RequestParam("contactPhone") String contactPhone,

            @NotBlank(message = "申请日期（APPLICATION_DATE）不能为空，格式：yyyy-MM-dd")
            @Pattern(regexp = "^\\d{4}-\\d{2}-\\d{2}$", message = "申请日期格式错误")
            @RequestParam("applicationDate") String applicationDateStr,

            @NotBlank(message = "项目名称（PROJECT_NAME）不能为空")
            @RequestParam("projectName") String projectName,

            // 其他字段
            @RequestParam(value = "position", required = false) String position,
            @Pattern(regexp = "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,6}$", message = "邮箱格式错误")
            @RequestParam(value = "email", required = false) String email,
            @RequestParam(value = "reportMainItemNo", required = false) String reportMainItemNo,
            @Pattern(regexp = "^(是|否)$", message = "是否超红线只能是'是'或'否'")
            @RequestParam(value = "isBeyondRedline", required = false, defaultValue = "否") String isBeyondRedline,
            @NotNull(message = "项目投资（PROJECT_INVESTMENT）不能为空")
            @RequestParam("projectInvestment") BigDecimal projectInvestment,
            @NotBlank(message = "项目类型（PROJECT_TYPE）不能为空")
            @RequestParam("projectType") String projectType,
            @NotBlank(message = "项目性质（PROJECT_NATURE）不能为空")
            @RequestParam("projectNature") String projectNature,
            @NotBlank(message = "立项理由（APPROVAL_REASON）不能为空")
            @RequestParam("approvalReason") String approvalReason,
            @NotBlank(message = "项目内容（PROJECT_CONTENT）不能为空")
            @RequestParam("projectContent") String projectContent,
            @RequestParam(value = "boardApprovalDesc", required = false) String boardApprovalDesc,
            @RequestParam(value = "sbqgsGgsFile", required = false) MultipartFile sbqgsGgsFile,
            @RequestParam(value = "fujianFile", required = false) MultipartFile fujianFile
    ) {
        Map<String, Object> result = new HashMap<>();
        long startTime = System.currentTimeMillis();

        try {
            // 处理日期
            LocalDate applicationDate = LocalDate.parse(applicationDateStr, DATE_FORMATTER);

            // 处理文件上传（返回文件详细信息，用于插入PB_DOCFILES）
            FileInfo sbqgsFileInfo = handleFileUpload(sbqgsGgsFile, "sbqgs-ggs");
            FileInfo fujianFileInfo = handleFileUpload(fujianFile, "fujian");

            // 插入主表和文件表（事务保证一致性）
            insertOaApplicationAndDocFiles(
                    applicantName, applicantDept, position, contactPhone, applicationDate,
                    email, reportMainItemNo, isBeyondRedline, projectName, projectInvestment,
                    projectType, projectNature, approvalReason, projectContent, boardApprovalDesc,
                    sbqgsFileInfo, fujianFileInfo
            );

            // 响应结果
            result.put("success", true);
            result.put("message", "成功");
            result.put("duration", System.currentTimeMillis() - startTime);
            logger.info("成功，耗时: {}ms", System.currentTimeMillis() - startTime);

        } catch (DateTimeParseException e) {
            logger.error("申请日期解析失败: {}", applicationDateStr, e);
            result.put("success", false);
            result.put("message", "提交失败：申请日期格式错误（正确格式：yyyy-MM-dd）");
            result.put("duration", System.currentTimeMillis() - startTime);
        } catch (IOException e) {
            logger.error("文件上传失败", e);
            result.put("success", false);
            result.put("message", "提交失败：文件上传异常，请检查文件或存储路径");
            result.put("duration", System.currentTimeMillis() - startTime);
        } catch (IllegalArgumentException e) {
            logger.error("参数校验失败", e);
            result.put("success", false);
            result.put("message", "提交失败：" + e.getMessage());
            result.put("duration", System.currentTimeMillis() - startTime);
        } catch (Exception e) {
            logger.error("系统异常导致提交失败", e);
            result.put("success", false);
            result.put("message", "提交失败：系统内部错误，请联系管理员");
            result.put("duration", System.currentTimeMillis() - startTime);
        }

        return result;
    }


    /**
     * 处理文件上传（返回文件详细信息，用于PB_DOCFILES插入）
     */
    private FileInfo handleFileUpload(MultipartFile file, String fileType) throws IOException {
        if (file == null || file.isEmpty()) {
            return null;
        }

        String originalFileName = file.getOriginalFilename();
        if (originalFileName == null) {
            throw new IllegalArgumentException(fileType + "文件名称为空，无法上传");
        }

        // 解析文件后缀
        int dotIndex = originalFileName.lastIndexOf(".");
        if (dotIndex == -1) {
            throw new IllegalArgumentException(fileType + "文件无后缀，不支持上传（允许格式：" + ALLOWED_FILE_SUFFIX + "）");
        }
        String fileSuffix = originalFileName.substring(dotIndex).toLowerCase();
        if (!ALLOWED_FILE_SUFFIX.contains(fileSuffix)) {
            throw new IllegalArgumentException(fileType + "文件类型不支持，允许：" + ALLOWED_FILE_SUFFIX);
        }

        // 构建存储路径
        String dateDir = LocalDate.now().format(DateTimeFormatter.ofPattern("yyyyMMdd"));
        File targetDir = new File(new File(fileBasePath), dateDir);
        if (!targetDir.exists() && !targetDir.mkdirs()) {
            throw new IOException("无法创建文件存储目录：" + targetDir.getAbsolutePath() + "（请检查权限）");
        }

        // 生成唯一文件名
        String uniqueFileName = UUID.randomUUID().toString() + fileSuffix;
        File targetFile = new File(targetDir, uniqueFileName);
        file.transferTo(targetFile);

        // 返回文件详细信息（用于插入PB_DOCFILES）
        return new FileInfo(
                originalFileName,          // 原始文件名（NAME）
                fileSuffix,                // 文件后缀（FILEEXT）
                file.getSize(),            // 文件大小（FILESIZE）
                dateDir + "/" + uniqueFileName  // 文件路径（SERVERURL）
        );
    }


    /**
     * 插入OA主表和PB_DOCFILES表（事务保证，插入前打印SQL）
     */
    @Transactional
    public void insertOaApplicationAndDocFiles(
            String applicantName, String applicantDept, String position, String contactPhone, LocalDate applicationDate,
            String email, String reportMainItemNo, String isBeyondRedline, String projectName, BigDecimal projectInvestment,
            String projectType, String projectNature, String approvalReason, String projectContent, String boardApprovalDesc,
            FileInfo sbqgsFileInfo, FileInfo fujianFileInfo
    ) {
        // 1. 生成OA主表ID（全球唯一）
        String oaId = UUID.randomUUID().toString();

        // 2. 插入OAJK_APPLICATION主表（先打印SQL和参数，再执行插入）
        String oaInsertSql = "INSERT INTO OAJK_APPLICATION (" +
                "ID, " +
                "APPLICANT_NAME, APPLICANT_DEPT, POSITION, CONTACT_PHONE, APPLICATION_DATE, " +
                "EMAIL, REPORT_MAIN_ITEM_NO, IS_BEYOND_REDLINE, PROJECT_NAME, PROJECT_INVESTMENT, " +
                "PROJECT_TYPE, PROJECT_NATURE, APPROVAL_REASON, PROJECT_CONTENT, BOARD_APPROVAL_DESC, " +
                "SBQGS_GGS, FUJIAN" +
                ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        // 组装OA主表插入参数（顺序与SQL占位符严格对应）
        Object[] oaParams = new Object[]{
                oaId,
                applicantName,
                applicantDept,
                position,
                contactPhone,
                java.sql.Date.valueOf(applicationDate),
                email,
                reportMainItemNo,
                isBeyondRedline,
                projectName,
                projectInvestment,
                projectType,
                projectNature,
                approvalReason,
                projectContent,
                boardApprovalDesc,
                sbqgsFileInfo != null ? sbqgsFileInfo.serverUrl : null,
                fujianFileInfo != null ? fujianFileInfo.serverUrl : null
        };

        // 【插入前打印】OA主表SQL和参数
        logger.info("【准备插入OA主表】SQL语句：{}", oaInsertSql);
        logger.info("【准备插入OA主表】参数列表：oaId={}, applicantName={}, applicantDept={}, contactPhone={}, projectName={}, 完整参数：{}",
                oaId, applicantName, applicantDept, contactPhone, projectName, Arrays.toString(oaParams));

        // 执行OA主表插入
        jdbcTemplate.update(oaInsertSql, oaParams);
        logger.info("【OA主表插入完成】ID: {}", oaId);

        // 3. 插入PB_DOCFILES表（有文件时才插入，插入前打印SQL）
        if (sbqgsFileInfo != null) {
            insertIntoPbDocFiles(oaId, sbqgsFileInfo, "sbqgs-ggs文件");
        }
        if (fujianFileInfo != null) {
            insertIntoPbDocFiles(oaId, fujianFileInfo, "附件文件");
        }
    }


    /**
     * 【修改后】插入单个文件到PB_DOCFILES表（匹配需求SQL格式，仅文件存在时执行）
     * @param fileTypeDesc 文件类型描述（用于日志区分）
     */
    private void insertIntoPbDocFiles(String folderId, FileInfo fileInfo, String fileTypeDesc) {
        // 1. 生成PB_DOCFILES唯一ID（保留原UUID逻辑，确保唯一性）
        String docId = UUID.randomUUID().toString();

        // 2. 处理当前时间：格式化为"yyyy-MM-dd HH:mm:ss"（匹配TO_DATE函数参数）
        String currentDateTime = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

        // 3. 调整后PB_DOCFILES插入SQL（完全匹配需求格式）
        String docInsertSql = "INSERT INTO PB_DOCFILES (" +
                "ID, FOLDERID, BOKEYWORD, NAME, FILEEXT, " +
                "FILESIZE, SERVERURL, VERSIONKEYVALUE, " +
                "REGHUMID, REGHUMNAME, REGDATE" +
                ") VALUES (" +
                "?, ?, ?, ?, ?, " +
                "?, ?, ?, " +
                "?, ?, TO_DATE(?, 'YYYY-MM-DD HH24:mi:ss')" +
                ")";

        // 4. 组装参数数组（顺序与SQL字段严格对应，移除FILEID/BIZAREAID）
        Object[] docParams = new Object[]{
                docId,                  // ID：全球唯一UUID
                folderId,               // FOLDERID：关联OA主表ID
                BOKEYWORD,              // BOKEYWORD：固定为OAJK_APPLICATION
                fileInfo.originalName,  // NAME：文件原始名称（如“文件一”）
                fileInfo.fileExt,       // FILEEXT：文件后缀（如“.doc”）
                fileInfo.fileSize,      // FILESIZE：文件大小（字节，如10240）
                fileInfo.serverUrl,     // SERVERURL：文件存储路径（如“20251112/xxx.doc”）
                docId,                  // VERSIONKEYVALUE：与ID一致（保留原逻辑）
                REGHUMID,               // REGHUMID：固定值（ad000000-...）
                REGHUMNAME,             // REGHUMNAME：固定值（系统管理员）
                currentDateTime         // REGDATE：当前时间（适配TO_DATE函数）
        };

        // 5. 日志打印（同步调整参数信息，移除FILEID相关内容）
        logger.info("【准备插入{}到PB_DOCFILES】SQL语句：{}", fileTypeDesc, docInsertSql);
        logger.info("【准备插入{}到PB_DOCFILES】核心参数：docId={}, folderId={}（关联OA主表ID）, fileName={}, fileSize={}字节, 插入时间={}",
                fileTypeDesc, docId, folderId, fileInfo.originalName, fileInfo.fileSize, currentDateTime);
        logger.info("【准备插入{}到PB_DOCFILES】完整参数列表：{}", fileTypeDesc, Arrays.toString(docParams));

        // 6. 执行插入（事务由上层@Transactional保证）
        jdbcTemplate.update(docInsertSql, docParams);
        logger.info("【{}插入PB_DOCFILES完成】文件ID: {}, 关联OA ID: {}", fileTypeDesc, docId, folderId);
    }

    /**
     * 内部类：存储文件详细信息（用于传递给PB_DOCFILES插入方法）
     */
    private static class FileInfo {
        String originalName;  // 原始文件名
        String fileExt;       // 文件后缀
        long fileSize;        // 文件大小（字节）
        String serverUrl;     // 文件存储路径

        public FileInfo(String originalName, String fileExt, long fileSize, String serverUrl) {
            this.originalName = originalName;
            this.fileExt = fileExt;
            this.fileSize = fileSize;
            this.serverUrl = serverUrl;
        }
    }
}